package gas.controller;

import gas.model.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Random;

public class DatabaseController {
    public static Connection conn;
    
    public static void cleanDB()throws Exception{
        Statement stat = conn.createStatement();
        stat.executeUpdate("drop table if exists users;");
        stat.executeUpdate("drop table if exists userPrefs;");
        stat.executeUpdate("drop table if exists gasStations;");
        stat.executeUpdate("create table users (username, password, email, ID, isAdmin);");
        stat.executeUpdate("create table userPrefs(ID, proximity, cheapness, time, location);");
        stat.executeUpdate("create table gasStations(stationName, currentPrice, location);");
    }
    public static void closeDB()throws Exception{
        conn.close();
    }
    
    private static void prepState(PreparedStatement ps, String[] sa)throws SQLException{
      for( int i = 1; i <= sa.length; i++)
        ps.setString(i, sa[i-1]);
      ps.addBatch();
    }
    
    public static void dummyDB(Object o)throws SQLException{
      /*** DUMMY DATA ***/
          System.out.print("[DatabaseController] Storing DummyData into DB...");
          PreparedStatement prep = conn.prepareStatement(
                     "insert into users values (?, ?, ?, ?, ?);");
              //                     username, password, email, ID, is Admin
              prep.setString(1, "Gandhi");
              prep.setString(2, "WorldPeace23!");
              prep.setString(3, "WorldPeace@gmail.com");
              prep.setString(4, "1023");
              prep.setString(5, "false");
              prep.addBatch();
              prep.setString(1, "Turing");
              prep.setString(2, "Computers32!");
              prep.setString(3, "alanT@gmail.com");
              prep.setString(4, "4567");
              prep.setString(5, "true");
              prep.addBatch();
          conn.setAutoCommit(false);
          prep.executeBatch();
          conn.setAutoCommit(true);
              
          PreparedStatement prep2 = conn.prepareStatement(
                  "insert into gasStations values (?, ?, ?);");
//                    stationName, currentPrice, location
                  prep2.setString(1, "Shell");
                  prep2.setString(2, "403");
                  prep2.setString(3, LocationsEnum.SANDIEGO.location());
                  prep2.addBatch();
                  prep2.setString(1, "Chevron");
                  prep2.setString(2, "323");
                  prep2.setString(3, LocationsEnum.LAJOLLA.location());
                  prep2.addBatch();
                  prep2.setString(1, "RichiesRichGas");
                  prep2.setString(2, "999");
                  prep2.setString(3, LocationsEnum.MARS.location());
                  prep2.addBatch();
                  prep2.setString(1, "76");
                  prep2.setString(2, "103");
                  prep2.setString(3, LocationsEnum.CHINA.location());
                  prep2.addBatch();
                  prep2.setString(1, "Costco");
                  prep2.setString(2, "311");
                  prep2.setString(3, LocationsEnum.LAJOLLA.location());
                  prep2.addBatch();
                  String[] sArr = { "Police", "911" , LocationsEnum.SANDIEGO.location()};
                  prepState(prep2, sArr);
                  sArr = new String[]{"Exxon", "375" , LocationsEnum.MEXICO.location() };
                  prepState(prep2, sArr);
                  sArr = new String[]{"CheapGas", "111" , LocationsEnum.SANJOSE.location() };
                  prepState(prep2, sArr);
                  sArr = new String[]{"Chevron", "321" , LocationsEnum.SANDIEGO.location() };
                  prepState(prep2, sArr);
                  sArr = new String[]{"76", "760" , LocationsEnum.LAJOLLA.location() };
                  prepState(prep2, sArr);
                                     //NAME  //Price in cents  // location
                  sArr = new String[]{"Limited Oil", "375" , LocationsEnum.SANDIEGO.location() };
                  prepState(prep2, sArr);
                  sArr = new String[]{"4everAlone", "10000" , LocationsEnum.NOWHERE.location() };
                  prepState(prep2, sArr);
                  
          conn.setAutoCommit(false);
          prep2.executeBatch();
          conn.setAutoCommit(true);
          System.out.println("done.");
          
          PreparedStatement prep3 = conn.prepareStatement(
                      "insert into userPrefs values (?, ?, ?, ?, ?);");
            //            ID, proximity, cheapness, time, location
                      prep3.setString(1, "1023");
                      prep3.setString(2, "3");
                      prep3.setString(3, "5");
                      prep3.setString(4, "8");
                      prep3.setString(5, LocationsEnum.NOWHERE.location()); // England
                      prep3.addBatch();
                      prep3.setString(1, "4567");
                      prep3.setString(2, "8");
                      prep3.setString(3, "3");
                      prep3.setString(3, "5");
                      prep3.setString(5, LocationsEnum.CHINA.location()); // England
                      prep3.addBatch();
          conn.setAutoCommit(false);
          prep3.executeBatch();
          conn.setAutoCommit(true);
          System.out.println("done.");
    }
    
    /***
     * This function stores (dummy) data from the program's data structures
     * into the Database.
     * @throws SQLException
     */
    public static void storeDB(Object o)throws SQLException{
        Random randomIDGen = new Random();
      /*** DUMMY DATA ***/
          System.out.print("[DatabaseController] Storing into DB...");
          PreparedStatement prep = conn.prepareStatement(
                     "insert into users values (?, ?, ?, ?, ?);");
          PreparedStatement prepPrefs = conn.prepareStatement(
                      "insert into userPrefs values ( ?, ?, ?, ?, ?);");
//                    ID, proximity, cheapness, time
              //     username, password, email, ID, isAdmin
          for ( Map.Entry<Integer, User> userEntry : ModelController.getUserBase().getUserMap().entrySet() ){
              User u = userEntry.getValue();
//            System.err.println(u.toString());
//            System.err.println(u.getPrefs().toString());
              
              prep.setString(1, u.getUsername());
              prep.setString(2, u.getPassword());
              prep.setString(3, u.getEmail() );
              prep.setString(4, userEntry.getKey().toString() );
              prep.setString(5, new Boolean(u.isAdmin()).toString() );
              prep.addBatch();
              
              prepPrefs.setString(1, userEntry.getKey().toString() );
              prepPrefs.setString(2, new Integer(u.getPrefs().getProximityValue()).toString());
              prepPrefs.setString(3, new Integer(u.getPrefs().getCostValue()).toString());
              prepPrefs.setString(4, new Integer(u.getPrefs().getWaitTolerance()).toString());
              prepPrefs.setString(5, u.getPrefs().getLocation());
              prepPrefs.addBatch();
              
          }
          conn.setAutoCommit(false);
          prep.executeBatch();
          prepPrefs.executeBatch();
          conn.setAutoCommit(true);
              
          PreparedStatement prep2 = conn.prepareStatement(
                  "insert into gasStations values (?, ?, ?);");
//                    stationName, currentPrice, location
              for ( GasStation gs : ModelController.getStationBase().getGasTable() ){
                  prep2.setString(1, gs.getStationName());
                  prep2.setString(2, new Integer(gs.getCurrentPrice()).toString() );
                  prep2.setString(3, gs.getLocation() );
                  prep2.addBatch();
              }
          conn.setAutoCommit(false);
          prep2.executeBatch();
          conn.setAutoCommit(true);
          System.out.print("done.");
    }
    /***
     * This function loads from the database into the ModelController.
     * @throws SQLException
     */
    public static void loadDB()throws SQLException{
            Statement stat = conn.createStatement();
            
            ResultSet rs = stat.executeQuery("select * from users;");
            while (rs.next()) {
              String dbUN = rs.getString("username");
              String dbPW = rs.getString("password");
              String dbEM = rs.getString("email");
              String dbID = rs.getString("ID");
              String dbisAdmin = rs.getString("isAdmin");
              ModelController.createUser(dbUN, dbPW, dbEM, Integer.valueOf(dbID), Boolean.valueOf(dbisAdmin), false);
            }
            System.out.print("\nPrinting UserBase:");
            for( User u : ModelController.getUserBase().getUserMap().values() )
                System.out.println(u.toString());
            
            /***
             *  TODO: Load Preferences...
             *  Match userID to user.
             */
            Statement stat3 = conn.createStatement();
            ResultSet rs3 = stat.executeQuery("select * from userPrefs;");
//          ID, proximity, cheapness, time
            while (rs3.next()) {
                String dbID = rs.getString("ID");
                String dbPx = rs.getString("proximity");
                String dbCp = rs.getString("cheapness");
                String dbWt = rs.getString("time");
                String dbLc = rs.getString("location");

                UserBase prefUpdater = ModelController.getUserBase(); //(dbUN, dbPW, dbEM, Integer.valueOf(dbID), Boolean.valueOf(dbisAdmin), false);
                for ( Map.Entry<Integer, User> userEntryIT : prefUpdater.getUserMap().entrySet()){
//                  System.err.print("\nCompare UserID: " + userEntryIT.getKey() + " to PrefID: " + Integer.valueOf(dbID));
                    if (Integer.valueOf(dbID).intValue()== userEntryIT.getKey().intValue()){
//                     System.err.print("\nSetting Preference for UserID: " + dbID);
                        userEntryIT.getValue().setPrefs(new Preferences( Integer.valueOf(dbPx).intValue(), 
                                Integer.valueOf(dbCp).intValue(), 0, Integer.valueOf(dbWt).intValue(), dbLc ));
                        break;
                    }
                }
            }
            
            System.out.print("\nPrinting UserBase With Prefs:");
            for( User u : ModelController.getUserBase().getUserMap().values() ){
                System.out.println(u.toString());
                System.out.println(u.getPrefs().toString());
            }
            
            Statement stat2 = conn.createStatement();
            ResultSet rs2 = stat2.executeQuery("select * from gasStations;");
//                                        stationName, currentPrice, location
            while (rs2.next()) {
                  
                  String dbUN = rs2.getString("stationName");
                  String dbPW = rs2.getString("currentPrice");
                  String dbEM = rs2.getString("location");
                  
                  ModelController.createStation(dbUN, dbPW, LocationsEnum.getMatch(dbEM));
            }
            
            System.out.println("\nPrinting StationBase:");
            for(int i = 0; i < ModelController.getStationBase().getGasTable().size(); i++ ){
                System.out.println(ModelController.getStationBase().getGasTable().get(i).toString());
            }
            System.out.println("[Start]: Finished loading from DB.");          
    }
}
